package com.ey12.file;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.ey12.utils.DateUtil;

/**
 * 
 * @author crystal
 */
public class FileUploaded extends HttpServlet {
	private static final String CONTENT_TYPE = "text/xml; charset=utf-8";
	private static String URL = "";
	private static String USN = "";
	private static String PWD = "";

	/**
	 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
	 * methods.
	 * 
	 * @param request
	 *            servlet request
	 * @param response
	 *            servlet response
	 */
	// // �����ļ����ϴ�·��
	// private String uploadPath = "d:\\a\\";
	// �����ļ����ϴ���С
	private int maxPostSize = 100 * 1024 * 1024;

	public FileUploaded() {
		super();
	}

	public void destroy() {
		super.destroy();
	}

	protected void processRequest(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		URL = this.getServletContext().getInitParameter("DbUrl");
		USN = this.getServletContext().getInitParameter("DbUsername");
		PWD = this.getServletContext().getInitParameter("DbPwd");
		String userid = request.getParameter("id");

		request.setCharacterEncoding("UTF-8");
		System.out.println("********************" + userid
				+ "*********************");
		System.out.println(request.getServletPath());
		System.out.println(request.getSession().getServletContext()
				.getRealPath(request.getRequestURI()));
		System.out.println("********************Access*********************");
		response.setContentType("text/html;charset=UTF-8");
		PrintWriter out = response.getWriter();
		String uploadDirectory = this.getServletContext().getInitParameter(
				"UploadDirectory");
		// �����ļ�����������
		ArrayList<String> allowedFormats = new ArrayList<String>();
		allowedFormats.add("csv");
		allowedFormats.add("xls");
		allowedFormats.add("xlsx");

		DiskFileItemFactory factory = new DiskFileItemFactory();
		factory.setSizeThreshold(8192);
		ServletFileUpload upload = new ServletFileUpload(factory);
		upload.setSizeMax(maxPostSize);
		try {
			List fileItems = upload.parseRequest(request);
			Iterator iter = fileItems.iterator();
			while (iter.hasNext()) {
				FileItem item = (FileItem) iter.next();
				if (!item.isFormField()) {
					String name = item.getName();
					System.out.println(name);
					String[] names = name.split("\\.");
					if (allowedFormats.contains(names[1])) {
						try {
							File file = new File(uploadDirectory + name);
							item.write(file);
							readExcel(userid, file);
						} catch (Exception e) {
							e.printStackTrace();
						}
					}
				}
			}
		} catch (FileUploadException e) {
			e.printStackTrace();
			System.out.println(e.getMessage() + "����");
		}
	}

	/**
	 * Handles the HTTP <code>GET</code> method.
	 * 
	 * @param request
	 *            servlet request
	 * @param response
	 *            servlet response
	 */
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		processRequest(request, response);
	}

	/**
	 * Handles the HTTP <code>POST</code> method.
	 * 
	 * @param request
	 *            servlet request
	 * @param response
	 *            servlet response
	 */
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		processRequest(request, response);
	}

	/**
	 * Returns a short description of the servlet.
	 */
	public String getServletInfo() {
		return "Short description";
	}

	public void readExcel(String userid, File file) {
		try {
			// 
			HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
			HSSFSheet sheet = workbook.getSheet("Sheet1");
			List list = new ArrayList();
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				HSSFRow row = sheet.getRow(i);
				HSSFCell game = row.getCell((short) 0);
				String gameValue = null;
				if(game!=null){
					gameValue = game.getStringCellValue();
				}else{
					continue;
				}
				HSSFCell cserver = row.getCell((short) 1);
				String cserverValue = cserver.getStringCellValue();
				HSSFCell servertype = row.getCell((short) 2);
				String servertypeValue = servertype.getStringCellValue();
				HSSFCell camp = row.getCell((short) 3);
				String campValue = camp.getStringCellValue();
				HSSFCell moneytype = row.getCell((short) 4);
				String moneytypeValue = moneytype.getStringCellValue();
				HSSFCell danweitype = row.getCell((short) 5);
				String danweiValue = danweitype.getStringCellValue();	
				HSSFCell price = row.getCell((short) 6);
				String priceValue = String.valueOf(price.getNumericCellValue());
				HSSFCell total = row.getCell((short) 7);
				String totalValue = String.valueOf(total.getNumericCellValue());
				HSSFCell lessbuy = row.getCell((short) 8);
				String lessbuyValue = String.valueOf(lessbuy
						.getNumericCellValue());
				if(lessbuyValue.indexOf(".")!=-1){
					lessbuyValue=lessbuyValue.split("\\.")[0];
				}
				HSSFCell quick = row.getCell((short) 9);
				String quickValue = quick.getStringCellValue();
				HSSFCell compensate = row.getCell((short) 10);
				String compensateValue = compensate.getStringCellValue();
				int gameid = findGameId(gameValue.trim());
				int gameLessBuy = findGameLessBuy(gameValue.trim());
				if (gameid != -1) {
					int isChecked = findIsChecked(Integer.parseInt(userid),
							gameid);
					if (isChecked == 2) {
						int cserverid = findCserverId(gameid, cserverValue,
								servertypeValue, moneytypeValue, campValue,danweiValue);
						if (cserverid != -1) {
							int lessbuyFinal=gameLessBuy;
							if(lessbuyValue!=null){
								if(!"".equals(lessbuyValue)){
									int lessbuyValueItem=Integer.parseInt(lessbuyValue);
									if(lessbuyValueItem>=gameLessBuy){
										lessbuyFinal=lessbuyValueItem;
									}																	
								}								
							}
							
							String sql = "";
							int storeid = findStoreExist(userid, gameid,
									cserverid);
							if (storeid == -1) {
								sql = "insert into gm_game_store(user_id,game_id,cserver_id,store_num,update_date,price,is_online,is_quick,is_deposit,least_mount,isDel) ";
								sql = sql + " values(" + userid + "," + gameid
										+ "," + cserverid + "," + totalValue
										+ ",'" + new DateUtil().toTimeString()
										+ "'," + priceValue + ",1,"
										+ reverse(quickValue) + ","
										+ reverse(compensateValue) + ","
										+ lessbuyFinal + ",1)";
							} else {//lessbuyValue //quickValue //compensateValue
								sql = "update gm_game_store set price="
										+ priceValue + ",store_num="
										+ totalValue + ",update_date='"
										+ new DateUtil().toTimeString()
										+ "',is_quick="+reverse(quickValue)+",is_deposit="+reverse(compensateValue)
										+",least_mount="+lessbuyFinal
										+",isDel=1 "
										+" where user_id=" + userid
										+ " and game_id=" + gameid
										+ " and cserver_id=" + cserverid;

							}
							// System.out.println("sql="+sql);
							list.add(sql);
						}
					}
				}
			}
			saveGmStore(list);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("������xlRead() : " + e);
		}

	}

	public int findStoreExist(String userid, int gameid, int cserverid) {
			int id = -1;
			
			String sql = "select * from gm_game_store where user_id=" + userid
					+ " and game_id=" + gameid + " and cserver_id=" + cserverid;
			try {
				Connection conn=getConnectionByJDBC();
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				if (rs.next()) {
					id = rs.getInt("id");
					
				}
				rs.close();
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				System.out.println(e.getMessage());
				e.printStackTrace();
			} 
			return id;
		}

	public String reverse(String item) {
		if ("是".equals(item)) {
			return "2";
		} else {
			return "1";
		}
	}

	public static Connection getConnectionByJDBC() {
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		try {
			conn = DriverManager.getConnection(URL, USN, PWD);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	public int findIsChecked(int userId, int gameId) {
		int is_checked = 1;

		String sqlx = "	select is_checked from gm_access where user_id="
				+ userId + " and game_id=" + gameId;
		try {
			Connection conn=getConnectionByJDBC();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlx);
			System.out.println("findIsChecked=" + sqlx);
			if (rs.next()) {
				is_checked = rs.getInt("is_checked");
				
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} 
		return is_checked;
	}

	public int findCserverId(int gameId, String cserverName, String severType,
			String unit, String camp,String danwei) {
		int id = -1;
		
		String sqlx = "select g.id from gm_cserver g where g.game_id=" + gameId
				+ " and g.cserver_name='" + cserverName
				+ "' and g.sever_type='" + severType + "' and g.unit='" + unit
				+ "' and g.camp='" + camp + "' and g.danwei='"+danwei+"'";
		try {
			Connection conn=getConnectionByJDBC();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlx);
			System.out.println("findCserverId="+sqlx);
			if (rs.next()) {
				id = rs.getInt("id");
				
			}
			// else {
			// String sql =
			// "insert into gm_cserver(game_id,cserver_name,sever_type,unit,camp) values("
			// + gameId
			// + ",'"
			// + cserverName
			// + "','"
			// + severType
			// + "','" + unit + "','" + camp + "');";
			// save(sql);
			// System.out.println(sql);
			// Statement stmt2 = conn.createStatement();
			// ResultSet rs2 = stmt2.executeQuery(sqlx);
			// if (rs2.next()) {
			// id = rs2.getInt("id");
			// }
			// stmt2.close();
			// }
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} 
		return id;
	}
	public int findGameLessBuy(String gameName) {
		int id = -1;
		
		String sqlx = "select least_mount from gm_game where game_name='" + gameName
				+ "'";
		try {
			Connection conn=getConnectionByJDBC();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlx);
			if (rs.next()) {
				id = rs.getInt("least_mount");
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
		return id;
	}
	public int findGameId(String gameName) {
		int id = -1;
		String sqlx = "select id from gm_game where game_name='" + gameName
				+ "'";
		try {
			Connection conn=getConnectionByJDBC();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlx);
			if (rs.next()) {
				id = rs.getInt("id");
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} 
		return id;
	}

	public void save(String sql) {
		try {
			Connection conn=getConnectionByJDBC();
			Statement stmt = conn.createStatement();
			stmt.execute(sql);
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} 
	}

	public void saveGmStore(List list) {
		try {

			Connection conn=getConnectionByJDBC();
			Statement stmt = conn.createStatement();

			for (int i = 0; i < list.size(); i++) {
				System.out.println(list.get(i).toString());
				stmt.execute(list.get(i).toString());
			}
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} 
	}

}
